/* Program name: auto_debt.sas;
* Objective: create an estimate of vehicle-related debt and student loan debt.;
* This code requires the clo variable, which is created in clean_tradeline04.sas, so cannot 
  be run on the raw tradeline files;
* Note: HARDCODED DATES BELOW;
*/

libname in '/Julie Holt-Baker';
libname scorein '/Julie Holt-Baker/';
%include "/Julie Holt-Baker/tu_formats.sas";
%include "/Julie Holt-Baker/06macros.sas";

data temp (keep = int teditseq terms hicredit acctype loantypt loantype acctbal ecoa crdtlim cll auto_: mortgage_: dtveri student_: ccard_: cctot_: install_: lcred_: unsec_: secgoods_: lease_: dtclose dtopen paypat mop permid2008 rename=(dtclose=dtclo));
  set in.cleantrade10;

  if clo eq 1 or dispcd in ('IA','INA','FTS','FTB','ETS','ETB','ETI') then coo = 1;
  else coo = 0;

  if dtclose gt 0 or coo eq 1 then cll = 1;
  else if acctype in ('I', 'M') and acctbal le 0 and mop eq 1 then cll = 1;
  else cll = 0;

  if acctbal lt 0 then acctbal = .;

/************************ADDED CODE 5/2019*****************/


/* installment loans */
  if loantypt in('IS') then do;
     loantype = "INSTALL"; 
     install_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     install_hist_ontime = paymop1_24;
     install_hist_3060 = pay3059_24;
     install_hist_6090 = pay6089_24;
     install_hist_90120 = pay90119_24;
     install_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then install_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 201001 then do;
	      if acctbal eq 0 then install_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(install);
	end;
     end;
  end;

/* line of credit */
  if loantypt in('LC') then do;
     loantype = "LCRED"; 
     lcred_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lcred_hist_ontime = paymop1_24;
     lcred_hist_3060 = pay3059_24;
     lcred_hist_6090 = pay6089_24;
     lcred_hist_90120 = pay90119_24;
     lcred_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lcred_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 201001 then do;
	      if acctbal eq 0 then lcred_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lcred);
	end;
     end;
  end;


/* Unsecured */
  if loantypt in('US') then do;
     loantype = "UNSEC"; 
     unsec_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     unsec_hist_ontime = paymop1_24;
     unsec_hist_3060 = pay3059_24;
     unsec_hist_6090 = pay6089_24;
     unsec_hist_90120 = pay90119_24;
     unsec_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then unsec_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 201001 then do;
	      if acctbal eq 0 then unsec_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(unsec);
	end;
     end;
  end;

/* Household goods secured & collateral */
  if loantypt in('SO','SH') then do;
     loantype = "SECGOODS"; 
     secgoods_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     secgoods_hist_ontime = paymop1_24;
     secgoods_hist_3060 = pay3059_24;
     secgoods_hist_6090 = pay6089_24;
     secgoods_hist_90120 = pay90119_24;
     secgoods_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then secgoods_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 201001 then do;
	      if acctbal eq 0 then secgoods_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(secgoods);
	end;
     end;
  end;


/* lease */
  if loantypt in('LE') then do;
     loantype = "LEASE"; 
     lease_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lease_hist_ontime = paymop1_24;
     lease_hist_3060 = pay3059_24;
     lease_hist_6090 = pay6089_24;
     lease_hist_90120 = pay90119_24;
     lease_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lease_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 201001 then do;
	      if acctbal eq 0 then lease_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lease);
	end;
     end;
  end;


/************************STOP ADDED CODE 5/2019*****************/

  * total auto_loans;
  if loantypt in('AU','AL','AR','AT','CA','MB','MT','RV') then do;
     loantype = "AUTO";
     auto_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline03.sas*/
     auto_hist_ontime = paymop1_24;
     auto_hist_3060 = pay3059_24;
     auto_hist_6090 = pay6089_24;
     auto_hist_90120 = pay90119_24;
     auto_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then auto_clo = 1;
	

     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 then do;
        if acctbal eq 0 then auto_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(auto);
	end; 
     end;
  end;

  

  * total student debt;
  if loantypt ='ST' then do;
     loantype = "STUD";
     student_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline03.sas*/
     student_hist_ontime = paymop1_24;
     student_hist_3060 = pay3059_24;
     student_hist_6090 = pay6089_24;
     student_hist_90120 = pay90119_24;
     student_hist_120plus = pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then student_clo = 1;
	

     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 then do;
        if acctbal eq 0 then student_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(student);
     	end;   
     end;
  end;

  /* total mortgage debt*/
  if loantypt in ('CV', 'FH', 'FI', 'FR', 'HE', 'HI', 'PI', 'RE', 'RL', 'RM', 'RT', 'SM', 'VA', 'VM','UK',' ') and acctype eq 'M' then do;
     loantype = "MORT";
     mortgage_open = 1;

     mortgage_hist_ontime = paymop1_24;
     mortgage_hist_3060 = pay3059_24;
     mortgage_hist_6090 = pay6089_24;
     mortgage_hist_90120 = pay90119_24;
     mortgage_hist_120plus = pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then mortgage_clo = 1;
	

     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 then do;
       if acctbal eq 0 then mortgage_clo = 1; 
       if acctbal gt 0 then do;
	   %recentcount(mortgage);
     	end;
     end;
  end;

  /* credit card debt (secured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC') then do;
     loantype = "CCAR"; 
     
     if ecoa = "I" then do;
      ccard_indiv_open = 1;
     	ccard_indiv_hist_ontime = paymop1_24;
	    ccard_indiv_hist_3060 = pay3059_24;
     	ccard_indiv_hist_6090 = pay6089_24;
     	ccard_indiv_hist_90120 = pay90119_24;
     	ccard_indiv_hist_120plus = pay120up_24;
     end;

     if ecoa = "C" then do;
     	ccard_joint_open = 1;
	    ccard_joint_hist_ontime = paymop1_24;
	    ccard_joint_hist_3060 = pay3059_24;
     	ccard_joint_hist_6090 = pay6089_24;
     	ccard_joint_hist_90120 = pay90119_24;
     	ccard_joint_hist_120plus = pay120up_24;
     end;

     if ecoa in ("M", "S", "C") then do;
     	ccard_cosign_open = 1;
	    ccard_cosign_hist_ontime = paymop1_24;
	    ccard_cosign_hist_3060 = pay3059_24;
     	ccard_cosign_hist_6090 = pay6089_24;
     	ccard_cosign_hist_90120 = pay90119_24;
     	ccard_cosign_hist_120plus = pay120up_24;
     end;

     if ecoa = "A" then do;
     	ccard_notliab_open = 1;
	    ccard_notliab_hist_ontime = paymop1_24;
	    ccard_notliab_hist_3060 = pay3059_24;
     	ccard_notliab_hist_6090 = pay6089_24;
     	ccard_notliab_hist_90120 = pay90119_24;
     	ccard_notliab_hist_120plus = pay120up_24;
     end;

     if ecoa in ("A", "P") then do;
     	ccard_maybeliab_open = 1;
	    ccard_maybeliab_hist_ontime = paymop1_24;
	    ccard_maybeliab_hist_3060 = pay3059_24;
     	ccard_maybeliab_hist_6090 = pay6089_24;
     	ccard_maybeliab_hist_90120 = pay90119_24;
     	ccard_maybeliab_hist_120plus = pay120up_24;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then ccard_indiv_clo = 1;
	  
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa = "I" then do;
     	%recentcount(ccard_indiv);
     end;

     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then ccard_joint_clo = 1;
	 
    /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa = "C" then do;
 	%recentcount(ccard_joint);
     end;
    
     * cosigner;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then ccard_cosign_clo = 1;

     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa in ("M", "S", "C") then do;
        %recentcount(ccard_cosign);
     end; 

     * not liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then ccard_notliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa in ("A") then do;
        %recentcount(ccard_notliab);
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then ccard_maybeliab_clo = 1;

     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa in ("A", "P") then do;
        %recentcount(ccard_maybeliab); 
     end; 

  end;

  /* credit card total debt (secured + unsecured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC','SC') then do;

     if ecoa = "I" then do;
     	cctot_indiv_open = 1;
     	cctot_indiv_hist_ontime = paymop1_24;
	    cctot_indiv_hist_3060 = pay3059_24;
     	cctot_indiv_hist_6090 = pay6089_24;
     	cctot_indiv_hist_90120 = pay90119_24;
     	cctot_indiv_hist_120plus = pay120up_24;
     end;

     if ecoa = "C" then do;
     	cctot_joint_open = 1;
	    cctot_joint_hist_ontime = paymop1_24;
	    cctot_joint_hist_3060 = pay3059_24;
     	cctot_joint_hist_6090 = pay6089_24;
     	cctot_joint_hist_90120 = pay90119_24;
     	cctot_joint_hist_120plus = pay120up_24;
     end;

     if ecoa in ("M", "S", "C") then do;
     	cctot_cosign_open = 1;
	    cctot_cosign_hist_ontime = paymop1_24;
	    cctot_cosign_hist_3060 = pay3059_24;
     	cctot_cosign_hist_6090 = pay6089_24;
     	cctot_cosign_hist_90120 = pay90119_24;
     	cctot_cosign_hist_120plus = pay120up_24;
     end;

     if ecoa = "A" then do;
     	cctot_notliab_open = 1;
	    cctot_notliab_hist_ontime = paymop1_24;
	    cctot_notliab_hist_3060 = pay3059_24;
     	cctot_notliab_hist_6090 = pay6089_24;
     	cctot_notliab_hist_90120 = pay90119_24;
     	cctot_notliab_hist_120plus = pay120up_24;
     end;

     if ecoa in ("A", "P") then do;
     	cctot_maybeliab_open = 1;
	    cctot_maybeliab_hist_ontime = paymop1_24;
	    cctot_maybeliab_hist_3060 = pay3059_24;
     	cctot_maybeliab_hist_6090 = pay6089_24;
     	cctot_maybeliab_hist_90120 = pay90119_24;
     	cctot_maybeliab_hist_120plus = pay120up_24;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then cctot_indiv_clo = 1;
	
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa = "I" then do;
     	%recentcount(cctot_indiv);
     end;

     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then cctot_joint_clo = 1;
 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa = "C" then do;
	%recentcount(cctot_joint);
     end;
    
     * cosigner;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then cctot_cosign_clo = 1;

     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa in ("M", "S", "C") then do;
        %recentcount(cctot_cosign);
     end; 

     * not liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then cctot_notliab_clo = 1;
 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa in ("A") then do;
        %recentcount(cctot_notliab); 
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then  cctot_maybeliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 201001 and ecoa in ("A", "P") then do;
        %recentcount(cctot_maybeliab);
     end; 
  end;

run;
 

proc sort data = temp;
  by teditseq;
run;
/*select variables that we want to get sum for collapsed dataset*/
proc sql;
     select name
     into :sumlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_debt' or name ? '_num' or name like '%_ontime' 
     or name like '_lim' or name like '%3060' or name like '%6090' or name like '%90120' 
     or name like '%120plus' or name like '%_worse'
     or name ? '_clo' or name ? '_open');
     quit;
/*select lot variables that we want to get min (choose earliest date)*/
proc sql;
     select name 
     into :minlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_lot' or name ? '_f120');
     quit;
/*collapse the dataset*/
 proc means data = temp noprint;
  by teditseq;
  var install: lcred: unsec: secgoods: lease: auto: trans: student: mortgage: ccard: cctot:;
  output out=rev_install_type10 min(&minlist)= sum(&sumlist)=;
run;

data rev_install_type10;
    set rev_install_type10;
    drop _type_ _freq_;

    ccard_indiv_joint_debt=sum(ccard_indiv_debt,.5*ccard_cosign_debt);
    cctot_indiv_joint_debt=sum(cctot_indiv_debt,.5*cctot_cosign_debt);

ccard_indiv_joint_lim=sum(ccard_indiv_lim,.5*ccard_cosign_lim);
    cctot_indiv_joint_lim=sum(cctot_indiv_lim,.5*cctot_cosign_lim);

run;

proc sort data=rev_install_type10; by teditseq; run;
proc sort data=temp; by teditseq; run;


data temp_new;


 merge temp (in = a keep= teditseq dtopen dtveri dtclo terms hicredit install_clo install_open lcred_clo lcred_open unsec_clo unsec_open secgoods_clo secgoods_open lease_clo lease_open student_clo student_open mortgage_clo mortgage_open auto_clo auto_open loantype student_debt int crdtlim acctbal ecoa
                    rename=(student_debt=student_debt_tl student_clo=student_clo_tl student_open=student_open_tl 
install_clo=install_clo_tl 
install_open = install_open_tl
lcred_clo = lcred_clo_tl
lcred_open = lcred_open_tl 
unsec_clo = unsec_clo_tl 
unsec_open = unsec_open_tl 
secgoods_clo = secgoods_clo_tl
secgoods_open = secgoods_open_tl
lease_clo = lease_clo_tl
lease_open = lease_open_tl 
terms=terms_tl 
hicredit=hicredit_tl 
mortgage_clo=mortgage_clo_tl 
                            mortgage_open=mortgage_open_tl auto_clo=auto_clo_tl auto_open=auto_open_tl crdtlim=crdtlim_tl acctbal=acctbal_tl ecoa=ecoa_tl))


rev_install_type10 (in = b);
by teditseq;      
  
if a; 
run;



/* pull roll up variables from SCORE dataset */
data roll_up10(drop=AT01);
     set scorein.rollups10 (keep= tueditsequencenumber RE28 RE34 AT33 AT28 ON33 MT33 IN33 RE33 AT01 TAM03TOT G041-G045 
     	 		   	  G057-G071 G082-G087 CBSA CensusBlockGroup CensusSuffix
				  CensusTract CountyFIPSCode Latitude Longitude StateFIPSCode 
				  ZIPCODE permid2008 
				  G104 G093-G094 G096 S059 S063-S064
     			    rename=(zipcode=ZIPCODE10num StateFIPSCode=STATE));
     rename TAM03TOT = tr_am;
     rename tueditsequencenumber = teditseq;
     ZIP10=put(ZIPCODE10num,$5.);
run;


proc sort data = roll_up10;
  by teditseq;
run;

data in.roll_trades10(rename=(teditseq=teditseq10));
     merge roll_up10
           temp_new ;
     by teditseq; /*merge must be unconditional on in=a or in=b*/
     
run;

proc print data=in.roll_trades10(obs=30);
run;

proc contents data=in.roll_trades10;
run;
